package dao;


import modle.users;
import util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 1、用于对用户账号密码数据库的各种操作
 * 2、只适用于MySQL数据库
 * 
 * @author Lzx
 *
 */

public class UserDaoJdbcImpl implements UserDAO {

	@Override
	public boolean writeUser(users user) {
		Connection conn = null;
		PreparedStatement pstat = null;
		System.out.println("::::::::writeUser::::::::::");
		String sq = "select * from users where Username = '" + user.getUsername() + "'";
		//表中有account和password这列
		String sql = "insert into users(Username,Password) values(?,?)";
		int result = -1;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sq);
			ResultSet rs = pstat.executeQuery();
			if( rs.next() ) {
				result = 0;
			}else {
				pstat = conn.prepareStatement(sql);
				pstat.setString(1,user.getUsername());
				pstat.setString(2,user.getPassword());
				result = pstat.executeUpdate();
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
		
	}

	@Override
	public users readUser(String Username) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		//表中有account和password这列
		System.out.println(":::::readerUser::::::");
		System.out.println("  username:" + Username);
		String sql = "select * from users where Username like ?";
		users user = null;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, Username);
			rs = pstat.executeQuery();
			while(rs.next()){
				String ac = rs.getString("Username");
				String ps = rs.getString("Password");
				System.out.println("ac: " + ac + " ps: " + ps);
				user = new users(ac, ps); 
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JdbcUtil.realeaseAll(rs,pstat, conn);
		}
		return user;
	}

	@Override
	public boolean changePassword(users user) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "update users set Password = ? where Username = ?";
		int result = -1;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1,user.getPassword());
			pstat.setString(2,user.getUsername());
			result = pstat.executeUpdate();
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
		
	}

	@Override
	public boolean deleteUser(String Username) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstat = null;
		//表中有account和password这列
		String sql = "delete from users where Username = ?";
		int result = -1;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1,Username);
			result = pstat.executeUpdate();
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public boolean changeUsername(String Username) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "update users set Username = ? where Username = ?";
		int result = -1;
		
		try {
			conn = JdbcUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1,Username);
			pstat.setString(2,Username);
			result = pstat.executeUpdate();
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

}
